import json
import os
import sys
import time
import uuid

import pymysql
from dbutils.pooled_db import PooledDB


class MySQL:
    pool = None
    limit_count = 5  # 最低预启动数据库连接数量

    def __init__(self):
        BASE_DIR = os.path.dirname(os.path.realpath(sys.argv[0]))
        fp = open(os.path.join(BASE_DIR, 'douyinhuoke.json'), encoding='utf-8')
        config_info = json.load(fp)
        fp.close()
        self.conn = None
        self.cursor = None
        self.pool = PooledDB(
            creator=pymysql,  # 使用链接数据库的模块
            mincached=10,  # 初始化时，链接池中至少创建的链接，0表示不创建
            maxconnections=200,  # 连接池允许的最大连接数，0和None表示不限制连接数
            blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
            host=config_info['host'],
            port=config_info['port'],
            user=config_info['user'],
            password=config_info['password'],
            database=config_info['db'],
        )

    def open(self):
        self.conn = self.pool.connection()
        self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)  # 表示读取的数据为字典类型
        return self.conn, self.cursor

    def close(self, cursor, conn):
        cursor.close()
        conn.close()

    def select_one(self, sql, *args):
        """查询单条数据"""
        conn, cursor = self.open()
        cursor.execute(sql, args)
        result = cursor.fetchone()
        self.close(conn, cursor)
        return result

    def select_all(self, sql, args):
        """查询多条数据"""
        conn, cursor = self.open()
        cursor.execute(sql, args)
        result = cursor.fetchall()
        self.close(conn, cursor)
        return result

    def insert_one(self, sql, args):
        """插入单条数据"""
        try:
            self.execute(sql, args, isNeed=True)
            return {'result': True, 'sql': sql}
        except Exception as err:
            return {'result': False, 'err': err}

    def insert_all(self, sql, datas):
        """插入多条批量插入"""
        conn, cursor = self.open()
        try:
            cursor.executemany(sql, datas)
            conn.commit()
            return {'result': True, 'id': int(cursor.lastrowid)}
        except Exception as err:
            conn.rollback()
            return {'result': False, 'err': err}

    def update_one(self, sql, args):
        """更新数据"""
        self.execute(sql, args, isNeed=True)

    def delete_one(self, sql, *args):
        """删除数据"""
        self.execute(sql, args, isNeed=True)

    def execute(self, sql, args, isNeed=False):
        """
        执行
        :param isNeed 是否需要回滚
        """
        conn, cursor = self.open()
        if isNeed:
            try:
                cursor.execute(sql, args)
                conn.commit()
            except:
                conn.rollback()
        else:
            cursor.execute(sql, args)
            conn.commit()
        self.close(conn, cursor)

    def get_mac(self):
        """ 获取服务器MAC地址 """
        mac = uuid.UUID(int=uuid.getnode()).hex[-12:]
        return ":".join([mac[e:e + 2] for e in range(0, 11, 2)])

    def load(self, offset=0, limit=0, type=""):
        host = self.get_mac()
        try:
            if limit == 0:
                if type != '':
                    sql = f'SELECT * FROM account where type = %s and host = %s'
                    results = self.select_all(sql, (type, host))
                else:
                    sql = f'SELECT * FROM account where %s'
                    results = self.select_all(sql, '1=1')
            else:
                if type != '':
                    sql = f'SELECT * FROM account where type = %s and host = %s'
                    results = self.select_all(sql, (type, host))
                else:
                    sql = f'SELECT * FROM account LIMIT = %s OFFSET = %s'
                    results = self.select_all(sql, (limit, offset))
            return [dict(row) for row in results]
        except Exception as e:
            return []

    def expire(self, id):
        ut = int(time.time())
        try:
            sql = f'UPDATE account SET expired = %s, ut = %s WHERE id = %s'
            self.update_one(sql, (1, ut, id))
            return True
        except Exception as e:
            return False
